package com.infodms.dms.common.materialManager;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;


import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.TcPosePO;
import com.infodms.dms.po.TmVhclMaterialGroupPO;
import com.infodms.dms.util.CommonUtils;
import com.infoservice.po3.POFactory;
import com.infoservice.po3.POFactoryBuilder;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;
import com.infoservice.po3.core.callback.DAOCallback;
import com.infoservice.po3.core.exceptions.DAOException;



public class MaterialGroupManagerDao extends BaseDao<PO>{
private static final MaterialGroupManagerDao dao = new MaterialGroupManagerDao ();
private static POFactory factory = POFactoryBuilder.getInstance();
public static final MaterialGroupManagerDao getInstance() {
	return dao;
}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		//List<Map<String, Object>> list=getGroupDropDownBox("1000001599","2");
		//System.out.print(list.size());
		System.out.print("1111");
	}
	/**
	 * Function         : 查询物料组下拉框共用方法
	 * @param           : 职位ID
	 * @param           : 显示物料等级
	 */
	public static List<Map<String, Object>> getGroupDropDownBox(String poseId,String level)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");
		sql.append("  FROM TM_BUSINESS_AREA       TBA,\n");  
		sql.append("       TM_POSE_BUSINESS_AREA  TPBA,\n");  
		sql.append("       TM_AREA_GROUP          TAP,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append(" WHERE TBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TPBA.AREA_ID = TAP.AREA_ID\n");  
		sql.append("   AND TAP.MATERIAL_GROUP_ID = TVMG.GROUP_ID\n");
		if(!"".equals(poseId)&&poseId!=null)
		{
			sql.append("   AND TPBA.POSE_ID = "+poseId+"\n");	
		}
		if(!"".equals(level)&&level!=null)
		{
			sql.append("   AND TVMG.GROUP_LEVEL = "+level+"\n");
		}
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	public static List<Map<String, Object>> getGroupDropDownBox(String poseId,String level,String companyId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();

		sql.append("SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append(" WHERE TVMG.GROUP_LEVEL = "+level+"\n");  
		sql.append("   AND TVMG.COMPANY_ID = "+companyId+"\n");  
		sql.append("   AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append(" START WITH TVMG.GROUP_ID IN\n");  
		sql.append("            (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("               FROM TM_AREA_GROUP TAG, TM_POSE_BUSINESS_AREA TPBA\n");  
		sql.append("              WHERE TAG.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("                AND TPBA.POSE_ID = "+poseId+")\n");  
		sql.append("CONNECT BY PRIOR TVMG.PARENT_GROUP_ID = TVMG.GROUP_ID\n");  
		sql.append("UNION\n");  
		sql.append("SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");  
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append(" WHERE TVMG.GROUP_LEVEL = "+level+"\n");  
		sql.append("   AND TVMG.COMPANY_ID = "+companyId+"\n");  
		sql.append("   AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append(" START WITH TVMG.GROUP_ID IN\n");  
		sql.append("            (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("               FROM TM_AREA_GROUP TAG, TM_POSE_BUSINESS_AREA TPBA\n");  
		sql.append("              WHERE TAG.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("                AND TPBA.POSE_ID = "+poseId+")\n");  
		sql.append("CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID");

		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	/**
	 * Function         : 查询经销商业务范围
	 * @param           : 职位ID
	 */
	public static List<Map<String, Object>> getDealerBusiness(String poseId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_CODE, TBA.AREA_ID, TBA.AREA_NAME, TD.DEALER_ID,TBA.AREA_TYPE\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");  
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n"); 
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA\n"); 
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		//sql.append("   AND TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+"\n");
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		if(!"".equals(poseId)&&poseId!=null)
		{
			sql.append("   AND TP.POSE_ID = "+poseId+"\n");;	
		}
		
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	/**
	 * Function         : 查询经销商业务范围
	 * @param           : 职位ID
	 */
	public static List<Map<String, Object>> getDealerBusiness(String poseId, String dlrLevel)
	{
		List<Map<String, Object>> list = null;
		
		StringBuffer sql=new StringBuffer("\n");
		
		sql.append("SELECT TBA.AREA_CODE, TBA.AREA_ID, TBA.AREA_NAME, TD.DEALER_ID,TBA.AREA_TYPE\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");  
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n"); 
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA\n"); 
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		
		if(!"".equals(poseId) && poseId != null)
		{
			sql.append("   AND TP.POSE_ID = " + poseId + "\n");;	
		}
		
		if(!CommonUtils.isNullString(dlrLevel)) {
			sql.append(" AND DEALER_LEVEL = ").append(dlrLevel).append("\n") ;
		}
		
		list = dao.pageQuery(sql.toString(), null, dao.getFunName());
		
		return list;
	}
	
	/**
	 * 查询业务范围和资金类型
	 * */
	public static List<Map<String, Object>> getDealerBusinessAndAccountType(String poseId){
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_CODE,\n");
		sql.append("       TBA.AREA_ID,\n");  
		sql.append("       TBA.AREA_NAME,\n");  
		sql.append("       TD.DEALER_ID,\n");  
		sql.append("       TVAT.TYPE_NAME,\n");  
		sql.append("       TVAT.TYPE_ID\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");  
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n");  
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA,\n");  
		sql.append("       TT_VS_ACCOUNT           TVA,\n");  
		sql.append("       TT_VS_ACCOUNT_TYPE      TVAT\n");  
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TD.DEALER_ID = TVA.DEALER_ID\n");  
		sql.append("   AND TVA.ACCOUNT_TYPE_ID = TVAT.TYPE_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n"); 
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		if(!"".equals(poseId)&&poseId!=null){
			sql.append("   AND TP.POSE_ID = "+poseId+"\n");;	
		}
		sql.append(" ORDER BY TBA.AREA_NAME DESC\n");

		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	public static List<Map<String, Object>> getJszxDealerBusiness(String poseId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_CODE, TBA.AREA_ID, TBA.AREA_NAME, TD.DEALER_ID\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");   
		sql.append("       TM_DEALER               TD1,\n"); 
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n"); 
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA\n"); 
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TD.PARENT_DEALER_D = TD1.DEALER_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		//sql.append("   AND TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+"\n");
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		sql.append("   AND TD1.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+"\n");
		if(!"".equals(poseId)&&poseId!=null)
		{
			sql.append("   AND TP.POSE_ID = "+poseId+"\n");;	
		}
		
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	/**
	 * 获得非结算中心业务范围
	 * @param poseId
	 * @return
	 */
	public static List<Map<String, Object>> getDealerBusinessNoJszx(String poseId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_CODE, TBA.AREA_ID, TBA.AREA_NAME, TD.DEALER_ID\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");   
		sql.append("       TM_DEALER               TD1,\n"); 
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n"); 
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA,VW_ORG_DEALER VOD\n"); 
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n"); 
		sql.append("   AND VOD.DEALER_ID = TD.DEALER_ID\n");
		sql.append("   AND VOD.ROOT_DEALER_ID = TD1.DEALER_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		//sql.append("   AND TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+"\n");
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		sql.append("   AND TD1.DEALER_TYPE <> "+Constant.DEALER_TYPE_JSZX+"\n");
		if(!"".equals(poseId)&&poseId!=null)
		{
			sql.append("   AND TP.POSE_ID = "+poseId+"\n");;	
		}
		
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	public static List<Map<String, Object>> getDealerBusinessAll(String poseId) {
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_ID, TBA.AREA_CODE, TBA.AREA_NAME, TPBA.POSE_ID\n");
		sql.append("  FROM TM_POSE_BUSINESS_AREA TPBA, TM_BUSINESS_AREA TBA\n");  
		sql.append(" WHERE TPBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND TPBA.POSE_ID = "+poseId+"\n");
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	/**
	 * Function         : 查询一级经销商业务范围
	 * @param           : 职位ID
	 */
	public static List<Map<String, Object>> getDealerLevelBusiness(String poseId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_CODE, TBA.AREA_ID, TBA.AREA_NAME, TD.DEALER_ID\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");  
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n"); 
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA\n"); 
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND TD.DEALER_LEVEL = "+Constant.DEALER_LEVEL_01+"\n");
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		if(!"".equals(poseId)&&poseId!=null)
		{
			sql.append("   AND TP.POSE_ID = "+poseId+"\n");;	
		}
		
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	/**
	 * Function         : 查询经销商业务范围id字符串
	 * @param           : 职位ID
	 */
	public static String getDealerBusinessIdStr(String poseId)
	{
		List<Map<String, Object>> list = getDealerBusiness(poseId);
		String areaIds = "";
		for (int i = 0; i < list.size(); i++) {
			BigDecimal temp = (BigDecimal) list.get(i).get("AREA_ID");
			areaIds += temp;
			if (i != list.size() - 1) {
				areaIds += ",";
			}
		}
		return areaIds;
	}
	
	public static String getDWRDealerBusinessIdStr(String poseId)
	{
		List<Map<String, Object>> list = getDealerBusinessAll(poseId);
		String areaIds = "";
		for (int i = 0; i < list.size(); i++) {
			BigDecimal temp = (BigDecimal) list.get(i).get("AREA_ID");
			areaIds += temp;
			if (i != list.size() - 1) {
				areaIds += ",";
			}
		}
		return areaIds;
	}
	
	/**
	 * Function         : 查询一级经销商业务范围id字符串
	 * @param           : 职位ID
	 */
	public static String getDealerLevelBusinessIdStr(String poseId)
	{
		List<Map<String, Object>> list = getDealerLevelBusiness(poseId);
		String areaIds = "";
		for (int i = 0; i < list.size(); i++) {
			BigDecimal temp = (BigDecimal) list.get(i).get("AREA_ID");
			areaIds += temp;
			if (i != list.size() - 1) {
				areaIds += ",";
			}
		}
		return areaIds;
	}
	public static String getMyPoseBusiness(String poseId){
		String areaId="";
		if(MaterialGroupManagerDao.getPoseIdBusiness(poseId).size()>0){
			for(int i=0;i<MaterialGroupManagerDao.getPoseIdBusiness(poseId).size();i++){
				if((i+1)!=MaterialGroupManagerDao.getPoseIdBusiness(poseId).size()){
					areaId=MaterialGroupManagerDao.getPoseIdBusiness(poseId).get(i).get("AREA_ID").toString()+","+areaId;
				}else{
					areaId=areaId+MaterialGroupManagerDao.getPoseIdBusiness(poseId).get(i).get("AREA_ID").toString();
				}
			}
		}	
		return areaId;
	}
	/**
	 * Function         : 查询车厂职位业务范围
	 * @param           : 职位ID
	 */
	public static List<Map<String, Object>> getPoseIdBusiness(String poseId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_ID, TBA.AREA_CODE, TBA.AREA_NAME, TPBA.POSE_ID\n");
		sql.append("  FROM TM_POSE_BUSINESS_AREA TPBA, TM_BUSINESS_AREA TBA\n");  
		sql.append(" WHERE TPBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND TPBA.POSE_ID = "+poseId+"\n");
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	
	/**
	 * Function         : 查询生产基地
	 * @param           : 职位ID
	 */
	public static List<Map<String, Object>> getProduceAdd()
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT DECODE(TBA.ERP_CODE,\n");
		sql.append("726,\n");
		sql.append("'重庆',\n");
		sql.append("82,\n");
		sql.append("'重庆',\n");
		sql.append("142,\n");
		sql.append("'河北',\n");
		sql.append("197,\n");
		sql.append("'南京') CODE_DESC\n");
		sql.append("FROM TM_BUSINESS_AREA TBA\n");
		sql.append("WHERE TBA.ERP_CODE IS NOT NULL\n");
		sql.append("GROUP BY TBA.ERP_CODE\n");
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	/**
	 * 查询当前经销商的职位
	 * 和业务范围
	 */
	public static List<Map<String, Object>> getPoseIdDealerBusiness(String poseId,String dealerId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_ID, TBA.AREA_CODE, TBA.AREA_NAME, TPBA.POSE_ID\n");
		sql.append("  FROM TM_POSE_BUSINESS_AREA TPBA, TM_BUSINESS_AREA TBA\n");  
		sql.append(" WHERE TPBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND TPBA.POSE_ID = "+poseId+"\n");
		sql.append(" AND TPBA.POSE_ID=TP.POSE_ID\n");
		sql.append(" AND TP.ORG_ID=TDOR.ORG_ID\n");
		sql.append(" AND TDOR.DEALER_ID IN("+dealerId+")\n");
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	
	
	
	
	
	/**
	 * Function         : 查询车厂职位业务范围id字符串
	 * @param           : 职位ID
	 */
	public static String getPoseIdBusinessIdStr(String poseId)
	{
		List<Map<String, Object>> list = getPoseIdBusiness(poseId);
		String areaIds = "";
		for (int i = 0; i < list.size(); i++) {
			BigDecimal temp = (BigDecimal) list.get(i).get("AREA_ID");
			areaIds += temp;
			if (i != list.size() - 1) {
				areaIds += ",";
			}
		}
		return areaIds;
	}
	
	public static TcPosePO getTcPosePO(Long poseId) {
		TcPosePO po = new TcPosePO();
		po.setPoseId(poseId);
		List<TcPosePO> list = factory.select(po);
		return list.size() != 0 ? list.get(0) : null;
	}
	
	public static String getAreaIdsByPoseId(Long poseId)
	{
		TcPosePO posePo = getTcPosePO(poseId);
		String areaIds = "";
		if(posePo!=null){
			// 判断职位是经销商还是车厂端
			if(posePo.getPoseType().intValue() == Constant.SYS_USER_SGM.intValue()){
				areaIds = getPoseIdBusinessIdStr(poseId.toString());
			}
			else{
				//add by liuqiang
				//判断如果是售后经销商,可以查看全部经销商,售后没有经销商用户选择经销商的功能,在车辆变更记录里加了这样的功能
				//所以这里做这样的判断,查询全部的经销商
				if (posePo.getPoseBusType().intValue() == Constant.POSE_BUS_TYPE_DWR) {
					areaIds = getDWRDealerBusinessIdStr(poseId.toString());
				} else {
					areaIds = getDealerBusinessIdStr(poseId.toString());
				}
			}
		}
		return areaIds;
	}
	
	/**
	 * Function         : 查询物料组弹出树
	 * @param           : 职位ID
	 */
	public static List<TmVhclMaterialGroupPO> getMaterialGroupTree(Long poseId,String groupLevel,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT GROUP_ID,GROUP_NAME,GROUP_CODE,PARENT_GROUP_ID\n");
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T\n");  
		sql.append(" WHERE T.STATUS="+Constant.STATUS_ENABLE+" \n"); 
		sql.append("  AND  T.COMPANY_ID="+companyId+" \n"); 
		
		if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		{
			sql.append(" AND T.GROUP_LEVEL <= "+groupLevel+"\n");
		}
		
		sql.append(" ORDER BY GROUP_ID \n");
				
		
		List<TmVhclMaterialGroupPO> list=factory.select(sql.toString(), null,
				new DAOCallback<TmVhclMaterialGroupPO>() {
			public TmVhclMaterialGroupPO wrapper(ResultSet rs, int idx) {
				TmVhclMaterialGroupPO bean = new TmVhclMaterialGroupPO();
				try {
					bean.setGroupId(Long.valueOf(rs.getString("GROUP_ID")));
					if(rs.getString("PARENT_GROUP_ID")!=null)
					{
						bean.setParentGroupId(Long.valueOf(rs.getString("PARENT_GROUP_ID")));	
					}else
					{
						bean.setParentGroupId(new Long(-1));
					}
					
					bean.setGroupName(rs.getString("GROUP_NAME"));
					bean.setGroupCode(rs.getString("GROUP_CODE"));
				} catch (SQLException e) {
					throw new DAOException(e);
				}
				return bean;
			}
		});
		return list;
	}
	
	/**
	 * Function         : 查询物料组弹出树过滤用户业务范围
	 * @param           : 职位ID
	 */
	public static List<TmVhclMaterialGroupPO> getMaterialGroupTreeByPoseArea(Long poseId,String groupLevel,Long companyId,String isAllArea)
	{
		StringBuffer sql=new StringBuffer();
		
		sql.append("select T.GROUP_ID, T.GROUP_CODE, T.GROUP_NAME, T.PARENT_GROUP_ID, T.GROUP_LEVEL\n");
		sql.append("  from tm_vhcl_material_group T\n");  
		sql.append(" WHERE T.STATUS = "+Constant.STATUS_ENABLE+"\n"); 
		sql.append("   AND T.COMPANY_ID = "+companyId+"\n");
		if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		{
			sql.append(" AND T.GROUP_LEVEL <= "+groupLevel+"\n");
		}
		if(!isAllArea.toLowerCase().equals("true")){
			String areaIds = getAreaIdsByPoseId(poseId);
			
			if(areaIds !=null && !areaIds.equals("")){
				sql.append(" AND T.GROUP_ID IN\n");
				sql.append("    (select T.group_id\n");  
				sql.append("       from tm_vhcl_material_group t\n");  
				sql.append("      start with T.group_id IN\n");  
				sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
				sql.append("                    FROM tm_area_group tap\n");  
				sql.append("                   where tap.area_id in ("+areaIds+"))\n");  
				sql.append("     connect by prior T.group_id = T.parent_group_id\n");  
				sql.append("     union\n");  
				sql.append("     select T.group_id\n");  
				sql.append("       from tm_vhcl_material_group t\n");  
				sql.append("      start with T.group_id IN\n");  
				sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
				sql.append("                    FROM tm_area_group tap\n");  
				sql.append("                   where tap.area_id in ("+areaIds+"))\n");
				sql.append("     connect by prior T.parent_group_id = T.group_id)");
			}
		}
		sql.append(" ORDER BY GROUP_ID \n");
		
		List<TmVhclMaterialGroupPO> list=factory.select(sql.toString(), null,
				new DAOCallback<TmVhclMaterialGroupPO>() {
			public TmVhclMaterialGroupPO wrapper(ResultSet rs, int idx) {
				TmVhclMaterialGroupPO bean = new TmVhclMaterialGroupPO();
				try {
					bean.setGroupId(Long.valueOf(rs.getString("GROUP_ID")));
					if(rs.getString("PARENT_GROUP_ID")!=null)
					{
						bean.setParentGroupId(Long.valueOf(rs.getString("PARENT_GROUP_ID")));	
					}else
					{
						bean.setParentGroupId(new Long(-1));
					}
					
					bean.setGroupName(rs.getString("GROUP_NAME"));
					bean.setGroupCode(rs.getString("GROUP_CODE"));
					bean.setGroupLevel(rs.getInt("GROUP_LEVEL"));
				} catch (SQLException e) {
					throw new DAOException(e);
				}
				return bean;
			}
		});
		return list;
	}
	/**
	 * Function         : 查询物料组弹出树:过滤已有物料组
	 * @param           : 职位ID
	 */
	public static List<TmVhclMaterialGroupPO> getMaterialCarTypeGroupTree_Sel(Long poseId,String groupLevel,String area_id,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("/*左侧树*/ \n");
		sql.append("SELECT T.GROUP_ID, \n");
		sql.append("       T.GROUP_CODE, \n");
		sql.append("       T.GROUP_NAME, \n");
		sql.append("       T.PARENT_GROUP_ID, \n");
		sql.append("       T.TREE_CODE \n");
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T\n");  
		sql.append(" WHERE T.STATUS="+Constant.STATUS_ENABLE+" \n"); 
		sql.append(" AND   T.COMPANY_ID="+companyId+" \n"); 
		//if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		//{
			sql.append(" AND T.GROUP_LEVEL <= "+3+"\n");
		//}
		
		if (null != area_id && !"".equals(area_id)) {
			sql.append("AND  EXISTS (SELECT TG.GROUP_ID \n");
			sql.append("          FROM TM_AREA_GROUP AG, TM_VHCL_MATERIAL_GROUP TG \n");
			sql.append("         WHERE AG.MATERIAL_GROUP_ID = TG.GROUP_ID \n");
			sql.append("           AND AG.AREA_ID IN( "+area_id+",2011012613562666"+") \n");
			sql.append("           AND TG.GROUP_ID = T.GROUP_ID) \n");
		}
		

		sql.append(" ORDER BY GROUP_ID \n");
		List<TmVhclMaterialGroupPO> list=factory.select(sql.toString(), null,
				new DAOCallback<TmVhclMaterialGroupPO>() {
			public TmVhclMaterialGroupPO wrapper(ResultSet rs, int idx) {
				TmVhclMaterialGroupPO bean = new TmVhclMaterialGroupPO();
				try {
					bean.setGroupId(Long.valueOf(rs.getString("GROUP_ID")));
					if(rs.getString("PARENT_GROUP_ID")!=null)
					{
						bean.setParentGroupId(Long.valueOf(rs.getString("PARENT_GROUP_ID")));	
					}else
					{
						bean.setParentGroupId(new Long(-1));
					}
					bean.setGroupName(rs.getString("GROUP_NAME"));
					bean.setGroupCode(rs.getString("GROUP_CODE"));
				} catch (SQLException e) {
					throw new DAOException(e);
				}
				return bean;
			}
		});
		return list;
	}
	/**
	 * Function         : 查询物料组弹出树:过滤已有物料组
	 * @param           : 职位ID
	 */
	public static List<TmVhclMaterialGroupPO> getMaterialGroupTree_Sel(Long poseId,String groupLevel,String area_id,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("/*左侧树*/ \n");
		sql.append("SELECT T.GROUP_ID, \n");
		sql.append("       T.GROUP_CODE, \n");
		sql.append("       T.GROUP_NAME, \n");
		sql.append("       T.PARENT_GROUP_ID, \n");
		sql.append("       T.TREE_CODE, \n");
		sql.append("       T.GROUP_LEVEL \n");
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T\n");  
		sql.append(" WHERE T.STATUS="+Constant.STATUS_ENABLE+" \n"); 
		sql.append(" AND   T.COMPANY_ID="+companyId+" \n"); 
		if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		{
			sql.append(" AND T.GROUP_LEVEL <= "+groupLevel+"\n");
		}
		
		if (null != area_id && !"".equals(area_id)) {
			sql.append("AND NOT EXISTS (SELECT TG.GROUP_ID \n");
			sql.append("          FROM TM_AREA_GROUP AG, TM_VHCL_MATERIAL_GROUP TG \n");
			sql.append("         WHERE AG.MATERIAL_GROUP_ID = TG.GROUP_ID \n");
			sql.append("           AND AG.AREA_ID = "+area_id+" \n");
			sql.append("           AND TG.GROUP_ID = T.GROUP_ID) \n");
		}
		

		sql.append(" ORDER BY GROUP_ID \n");
		List<TmVhclMaterialGroupPO> list=factory.select(sql.toString(), null,
				new DAOCallback<TmVhclMaterialGroupPO>() {
			public TmVhclMaterialGroupPO wrapper(ResultSet rs, int idx) {
				TmVhclMaterialGroupPO bean = new TmVhclMaterialGroupPO();
				try {
					bean.setGroupId(Long.valueOf(rs.getString("GROUP_ID")));
					if(rs.getString("PARENT_GROUP_ID")!=null)
					{
						bean.setParentGroupId(Long.valueOf(rs.getString("PARENT_GROUP_ID")));	
					}else
					{
						bean.setParentGroupId(new Long(-1));
					}
					bean.setGroupName(rs.getString("GROUP_NAME"));
					bean.setGroupCode(rs.getString("GROUP_CODE"));
					bean.setGroupLevel(rs.getInt("GROUP_LEVEL"));
				} catch (SQLException e) {
					throw new DAOException(e);
				}
				return bean;
			}
		});
		return list;
	}
	/**
	 * Function         : 查询配置
	 * @param           : 车系、车型、配置ID
	 * @param           : 车系、车型、配置CODE
	 * @param           : 车系、车型、配置名称
	 */
	public static PageResult<Map<String, Object>> getGroupList(String groupId,String groupCode,String groupName,String groupLevel,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT T.GROUP_ID,\n");
		sql.append("       T.GROUP_CODE,\n");  
		sql.append("       T.GROUP_NAME,\n");  
		sql.append("       T.PARENT_GROUP_ID,\n");  
		sql.append("       T.TREE_CODE\n");  
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T\n");  
		sql.append(" WHERE T.COMPANY_ID = "+companyId+"\n");  
		if(!"".equals(groupCode)&&groupCode!=null)
		{
		sql.append("   AND T.GROUP_CODE LIKE '%"+groupCode+"%'\n");	
		}
		if(!"".equals(groupName)&&groupName!=null)
		{
		sql.append("   AND T.GROUP_NAME LIKE '%"+groupName+"%'\n"); 
		}
		if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		{
		sql.append("   AND T.GROUP_LEVEL = "+groupLevel+"\n");
		}
		sql.append("   AND T.STATUS = "+Constant.STATUS_ENABLE+"\n");
		if(!"".equals(groupId)&&groupId!=null)
		{
			if("".equals(groupLevel)||groupLevel==null||"null".equals(groupLevel))
		  {
				sql.append(" AND  T.PARENT_GROUP_ID = "+groupId+"\n");  
		  }else
		  {
			   sql.append(" START WITH T.GROUP_ID = "+groupId+"\n");  
			   sql.append("CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID\n"); 	  
		  }
		
		}
		
		sql.append(" ORDER BY T.GROUP_ID\n");
		
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	
	/**
	 * Function         : 查询配置
	 * @param           : 车系、车型、配置ID
	 * @param           : 车系、车型、配置CODE
	 * @param           : 车系、车型、配置名称
	 */
	public static PageResult<Map<String, Object>> getGroupListByPoseArea(String poseId,String groupId,String groupCode,String groupName,String groupLevel,int curPage,int pageSize,Long companyId, String isAllArea)
	{
		StringBuffer sql = new StringBuffer("\n");
		boolean flag = false ;
		String areaIds = "" ;
		
		if(!isAllArea.toLowerCase().equals("true")){

			areaIds = getAreaIdsByPoseId(new Long(poseId));
	
			if(areaIds != null && !areaIds.equals("")){
				flag = true ;
			}
		}
		
		
		sql.append("SELECT T.GROUP_ID,\n");
		sql.append("       T.GROUP_CODE,\n");  
		sql.append("       T.GROUP_NAME,\n");  
		sql.append("       T.PARENT_GROUP_ID,\n");  
		sql.append("       T.TREE_CODE,\n");  
		sql.append("       T.Group_Level\n");
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T");  
		
		if(flag) {
			sql.append(",\n");
			sql.append("(select T.group_id\n");
			sql.append("          from tm_vhcl_material_group t\n");  
			sql.append("         start with T.group_id IN\n");  
			sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
			sql.append("                       FROM tm_area_group tap\n");  
			sql.append("                      where tap.area_id in (" + areaIds + "))\n");  
			sql.append("        connect by prior T.group_id = T.parent_group_id\n");  
			sql.append("        union\n");  
			sql.append("        select T.group_id\n");  
			sql.append("          from tm_vhcl_material_group t\n");  
			sql.append("         start with T.group_id IN\n");  
			sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
			sql.append("                       FROM tm_area_group tap\n");  
			sql.append("                      where tap.area_id in (" + areaIds + "))\n");  
			sql.append("        connect by prior T.parent_group_id = T.group_id) t2\n");
		}

		sql.append(" WHERE T.COMPANY_ID = " + companyId + "\n");
		
		if(!"".equals(groupCode)&&groupCode != null)
		{
			sql.append("   AND T.GROUP_CODE LIKE '%" + groupCode + "%'\n");	
		}
		if(!"".equals(groupName) && groupName != null)
		{
			sql.append("   AND T.GROUP_NAME LIKE '%" + groupName + "%'\n"); 
		}
		if(!"".equals(groupLevel) && groupLevel != null && !"null".equals(groupLevel))
		{
			sql.append("   AND T.GROUP_LEVEL = " + groupLevel + "\n");
		}
		
		sql.append("   AND T.STATUS = " + Constant.STATUS_ENABLE + "\n");
		
		if(flag) {
			sql.append("AND T.GROUP_ID = t2.GROUP_ID\n");
		}
		
		if(!"".equals(groupId) && groupId != null)
		{
			if("".equals(groupLevel) || groupLevel == null || "null".equals(groupLevel))
		  {
				sql.append(" AND  T.PARENT_GROUP_ID = " + groupId + "\n");  
		  }else
		  {
			  	sql.append(" START WITH T.GROUP_ID = " + groupId + "\n");  
			  	sql.append("CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID\n"); 	  
		  }
		
		}
		
		/*if(!isAllArea.toLowerCase().equals("true")){

			String areaIds = getAreaIdsByPoseId(new Long(poseId));
	
			if(areaIds!=null && !areaIds.equals("")){
				sql.append(" AND T.GROUP_ID IN\n");
				sql.append("    (select T.group_id\n");  
				sql.append("       from tm_vhcl_material_group t\n");  
				sql.append("      start with T.group_id IN\n");  
				sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
				sql.append("                    FROM tm_area_group tap\n");  
				sql.append("                   where tap.area_id in ("+areaIds+"))\n");  
				sql.append("     connect by prior T.group_id = T.parent_group_id\n");  
				sql.append("     union\n");  
				sql.append("     select T.group_id\n");  
				sql.append("       from tm_vhcl_material_group t\n");  
				sql.append("      start with T.group_id IN\n");  
				sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
				sql.append("                    FROM tm_area_group tap\n");  
				sql.append("                   where tap.area_id in ("+areaIds+"))\n");
				sql.append("     connect by prior T.parent_group_id = T.group_id)");
			}
		}*/
		
		sql.append(" ORDER BY T.GROUP_LEVEL, T.GROUP_CODE\n");
		
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	/**
	 * Function         : 查询配置:过滤已有物料组
	 * @param           : 车系、车型、配置ID
	 * @param           : 车系、车型、配置CODE
	 * @param           : 车系、车型、配置名称
	 */
	public static PageResult<Map<String, Object>> getGroupCarTypeList_Sel(String groupId,String groupCode,String groupName,String groupLevel,String area_id ,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("--右侧展示\n");
		sql.append("SELECT T.GROUP_ID,\n");
		sql.append("       T.GROUP_CODE,\n");  
		sql.append("       T.GROUP_NAME,\n");  
		sql.append("       T.PARENT_GROUP_ID,\n");  
		sql.append("       T.TREE_CODE\n");  
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T\n");  
		sql.append(" WHERE T.COMPANY_ID = "+companyId+"\n");  
		
//		if (null != treeCodeList && treeCodeList.size()>0) {
//			sql.append("  AND NOT EXISTS(SELECT TVG.TREE_CODE FROM TM_VHCL_MATERIAL_GROUP TVG WHERE 1=1  \n");
//			for (int i = 0; i < treeCodeList.size(); i++) {
//				sql.append("   OR  SUBSTR(TVG.TREE_CODE,0,"+((Map)treeCodeList.get(i)).get("CODE_LENGTH")+")='"+((Map)treeCodeList.get(i)).get("TREE_CODE")+"'\n");
//			}
//			sql.append("\n)");
//		}
		
		
		if (null != area_id && !"".equals(area_id)) {
			sql.append(" AND EXISTS \n");
			sql.append("                (SELECT 1 \n");
			sql.append("                   FROM (    SELECT TG.GROUP_ID \n");
			sql.append("                               FROM TM_VHCL_MATERIAL_GROUP TG \n");
			sql.append("                         START WITH TG.GROUP_ID IN \n");
			sql.append("                                       (SELECT AG.MATERIAL_GROUP_ID \n");
			sql.append("                                          FROM TM_AREA_GROUP AG \n");
			sql.append("                                         WHERE 1=1  \n");
			if(area_id!=null && !area_id.equals(" ")){
			sql.append(" AND AG.AREA_ID = "+area_id+"\n");
			}
			sql.append("                         )CONNECT BY PRIOR TG.GROUP_ID = TG.PARENT_GROUP_ID) DD \n");
			sql.append("                  WHERE DD.GROUP_ID = T.GROUP_ID) \n");

		}
		

		if(!"".equals(groupCode)&&groupCode!=null)
		{
		sql.append("   AND T.GROUP_CODE LIKE '%"+groupCode+"%'\n");	
		}
		if(!"".equals(groupName)&&groupName!=null)
		{
		sql.append("   AND T.GROUP_NAME LIKE '%"+groupName+"%'\n"); 
		}
		if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		{
		sql.append("   AND T.GROUP_LEVEL = "+groupLevel+"\n");
		}
		sql.append("   AND T.STATUS = "+Constant.STATUS_ENABLE+"\n");
		if(!"".equals(groupId)&&groupId!=null)
		{
			if("".equals(groupLevel)||groupLevel==null||"null".equals(groupLevel))
		  {
				sql.append(" AND  T.PARENT_GROUP_ID = "+groupId+"\n");  
		  }else
		  {
			   sql.append(" START WITH T.GROUP_ID = "+groupId+"\n");  
			   sql.append("CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID\n"); 	  
		  }
		
		}
		sql.append(" ORDER BY T.GROUP_ID\n");
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	
	/**
	 * Function         : 查询配置:过滤已有物料组
	 * @param           : 车系、车型、配置ID
	 * @param           : 车系、车型、配置CODE
	 * @param           : 车系、车型、配置名称
	 */
	public static PageResult<Map<String, Object>> getGroupList_Sel(String groupId,String groupCode,String groupName,String groupLevel,String area_id ,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("--右侧展示\n");
		sql.append("SELECT T.GROUP_ID,\n");
		sql.append("       T.GROUP_CODE,\n");  
		sql.append("       T.GROUP_NAME,\n");  
		sql.append("       T.PARENT_GROUP_ID,\n");  
		sql.append("       T.TREE_CODE,\n");  
		sql.append("       T.GROUP_LEVEL\n");  
		sql.append("  FROM TM_VHCL_MATERIAL_GROUP T\n");  
		sql.append(" WHERE T.COMPANY_ID = "+companyId+"\n");  
		
//		if (null != treeCodeList && treeCodeList.size()>0) {
//			sql.append("  AND NOT EXISTS(SELECT TVG.TREE_CODE FROM TM_VHCL_MATERIAL_GROUP TVG WHERE 1=1  \n");
//			for (int i = 0; i < treeCodeList.size(); i++) {
//				sql.append("   OR  SUBSTR(TVG.TREE_CODE,0,"+((Map)treeCodeList.get(i)).get("CODE_LENGTH")+")='"+((Map)treeCodeList.get(i)).get("TREE_CODE")+"'\n");
//			}
//			sql.append("\n)");
//		}
		
		
		if (null != area_id && !"".equals(area_id)) {
			sql.append(" AND NOT EXISTS \n");
			sql.append("                (SELECT 1 \n");
			sql.append("                   FROM (    SELECT TG.GROUP_ID \n");
			sql.append("                               FROM TM_VHCL_MATERIAL_GROUP TG \n");
			sql.append("                         START WITH TG.GROUP_ID IN \n");
			sql.append("                                       (SELECT AG.MATERIAL_GROUP_ID \n");
			sql.append("                                          FROM TM_AREA_GROUP AG \n");
			sql.append("                                         WHERE AG.AREA_ID = "+area_id+") \n");
			sql.append("                         CONNECT BY PRIOR TG.GROUP_ID = TG.PARENT_GROUP_ID) DD \n");
			sql.append("                  WHERE DD.GROUP_ID = T.GROUP_ID) \n");

		}
		

		if(!"".equals(groupCode)&&groupCode!=null)
		{
		sql.append("   AND T.GROUP_CODE LIKE '%"+groupCode+"%'\n");	
		}
		if(!"".equals(groupName)&&groupName!=null)
		{
		sql.append("   AND T.GROUP_NAME LIKE '%"+groupName+"%'\n"); 
		}
		if(!"".equals(groupLevel)&&groupLevel!=null&&!"null".equals(groupLevel))
		{
		sql.append("   AND T.GROUP_LEVEL = "+groupLevel+"\n");
		}
		sql.append("   AND T.STATUS = "+Constant.STATUS_ENABLE+"\n");
		if(!"".equals(groupId)&&groupId!=null)
		{
			if("".equals(groupLevel)||groupLevel==null||"null".equals(groupLevel))
		  {
				sql.append(" AND  T.PARENT_GROUP_ID = "+groupId+"\n");  
		  }else
		  {
			   sql.append(" START WITH T.GROUP_ID = "+groupId+"\n");  
			   sql.append("CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID\n"); 	  
		  }
		
		}
		sql.append(" ORDER BY T.GROUP_ID\n");
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	/**
	 * Function         : 查询物料
	 * @param           : 车系、车型、配置ID
	 * @param           : 物料CODE
	 * @param           : 物料名称
	 */
	public static PageResult<Map<String, Object>> getMaterialList(String groupId,String materialCode,String materialName,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TVM.MATERIAL_CODE,TVM.MATERIAL_ID,TVM.MATERIAL_NAME\n");
		sql.append("  FROM TM_VHCL_MATERIAL TVM\n");  
		sql.append(" WHERE TVM.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("  AND TVM.COMPANY_ID = "+companyId+"\n");
		if(!"".equals(materialCode)&&materialCode!=null)
		{
			sql.append("   AND TVM.MATERIAL_CODE LIKE '%"+materialCode+"%'\n");	
		}
		if(!"".equals(materialName)&&materialName!=null)
		{ 
		sql.append("   AND TVM.MATERIAL_NAME LIKE '%"+materialName+"%'\n");
		}
		if(!"".equals(groupId)&&groupId!=null){
			sql.append("   AND TVM.MATERIAL_ID IN\n");  
			sql.append("       (SELECT TVMGR.MATERIAL_ID\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
			sql.append("         WHERE TVMGR.GROUP_ID IN\n");  
			sql.append("               (SELECT TVMG.GROUP_ID\n");  
			sql.append("                  FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
			sql.append("                 START WITH TVMG.GROUP_ID = "+groupId+"\n");  
			sql.append("                CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID\n");  
			sql.append("                       AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"))\n");	
		}

		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	
	/**
	 * Function         : 查询物料
	 * @param           : 车系、车型、配置ID
	 * @param           : 物料CODE
	 * @param           : 物料名称
	 */
	public static PageResult<Map<String, Object>> getMaterialListByPoseArea(String poseId,String groupId,String materialCode,String materialName,int curPage,int pageSize,Long companyId,String isAllArea)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TVM.MATERIAL_CODE,TVM.MATERIAL_ID,TVM.MATERIAL_NAME\n");
		sql.append("  FROM TM_VHCL_MATERIAL TVM, TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
		sql.append(" WHERE TVM.MATERIAL_ID = TVMGR.MATERIAL_ID\n");
		sql.append("  AND TVM.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("  AND TVM.COMPANY_ID = "+companyId+"\n");
		if(!"".equals(materialCode)&&materialCode!=null)
		{
			sql.append("   AND TVM.MATERIAL_CODE LIKE '%"+materialCode+"%'\n");	
		}
		if(!"".equals(materialName)&&materialName!=null)
		{ 
		sql.append("   AND TVM.MATERIAL_NAME LIKE '%"+materialName+"%'\n");
		}
		if(!"".equals(groupId)&&groupId!=null){
			sql.append("   AND TVM.MATERIAL_ID IN\n");  
			sql.append("       (SELECT TVMGR.MATERIAL_ID\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
			sql.append("         WHERE TVMGR.GROUP_ID IN\n");  
			sql.append("               (SELECT TVMG.GROUP_ID\n");  
			sql.append("                  FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
			sql.append("                 START WITH TVMG.GROUP_ID = "+groupId+"\n");  
			sql.append("                CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID\n");  
			sql.append("                       AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"))\n");	
		}
		
		if(!isAllArea.toLowerCase().equals("true")){
			String areaIds = getAreaIdsByPoseId(new Long(poseId));
	
			sql.append(" AND TVMGR.GROUP_ID IN\n");
			sql.append("    (select T.group_id\n");  
			sql.append("       from tm_vhcl_material_group t\n");  
			sql.append("      start with T.group_id IN\n");  
			sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
			sql.append("                    FROM tm_area_group tap\n");  
			sql.append("                   where tap.area_id in ("+areaIds+"))\n");  
			sql.append("     connect by prior T.group_id = T.parent_group_id)\n");  
		}

		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	
	/**
	 * Function         : 查询物料组弹出树
	 * @param           : 职位ID
	 */
	public static List<TmVhclMaterialGroupPO> getMaterialGroupTreeByAreaId(String areaId,Long companyId, String groupLevel)
	{
		StringBuffer sql=new StringBuffer();

		sql.append("select T1.GROUP_ID, T1.GROUP_CODE, T1.GROUP_NAME, T1.PARENT_GROUP_ID, T1.GROUP_LEVEL\n");
		sql.append("  from tm_vhcl_material_group t1\n");  
		sql.append(" WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n"); 
		sql.append("   AND T1.COMPANY_ID = "+companyId+"\n"); 
		if(groupLevel!=null && !groupLevel.equals("")){
			sql.append("   AND T1.GROUP_LEVEL <= "+groupLevel+"\n"); 
		}
		sql.append(" start with t1.group_id IN\n");  
		sql.append("            (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("               FROM tm_area_group tap\n");  
		sql.append("              where tap.area_id = "+areaId+")\n");  
		sql.append("connect by prior t1.group_id = t1.parent_group_id\n");  
		sql.append("UNION\n");  
		sql.append("select T1.GROUP_ID, T1.GROUP_CODE, T1.GROUP_NAME, T1.PARENT_GROUP_ID, T1.GROUP_LEVEL\n");  
		sql.append("  from tm_vhcl_material_group t1\n");  
		sql.append(" WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND T1.COMPANY_ID = "+companyId+"\n");
		if(groupLevel!=null && !groupLevel.equals("")){
			sql.append("   AND T1.GROUP_LEVEL <= "+groupLevel+"\n"); 
		}
		sql.append(" start with t1.group_id IN\n");  
		sql.append("            (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("               FROM tm_area_group tap\n");  
		sql.append("              where tap.area_id = "+areaId+")\n");  
		sql.append("connect by prior t1.parent_group_id = t1.group_id");

		List<TmVhclMaterialGroupPO> list=factory.select(sql.toString(), null,
				new DAOCallback<TmVhclMaterialGroupPO>() {
			public TmVhclMaterialGroupPO wrapper(ResultSet rs, int idx) {
				TmVhclMaterialGroupPO bean = new TmVhclMaterialGroupPO();
				try {
					bean.setGroupId(Long.valueOf(rs.getString("GROUP_ID")));
					bean.setParentGroupId(Long.valueOf(rs.getString("PARENT_GROUP_ID")));
					bean.setGroupName(rs.getString("GROUP_NAME"));
					bean.setGroupCode(rs.getString("GROUP_CODE"));
					bean.setGroupLevel(rs.getInt("GROUP_LEVEL"));
				} catch (SQLException e) {
					throw new DAOException(e);
				}
				return bean;
			}
		});
		return list;
	}

	/**
	 * Function         : 查询物料组弹出树--微车
	 * @param           : 职位ID
	 */
	public static List<TmVhclMaterialGroupPO> getMaterialGroupTreeByAreaId_Mini(String areaId,Long companyId)
	{
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT DISTINCT T1.GROUP_ID,\n");
		sql.append("                T1.GROUP_CODE,\n");  
		sql.append("                T1.GROUP_NAME,\n");  
		sql.append("                T1.PARENT_GROUP_ID\n");  
		sql.append("  FROM (SELECT *\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP T2\n");  
		sql.append("         START WITH T2.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAP\n");  
		sql.append("                      WHERE TAP.AREA_ID = "+areaId+")\n");  
		sql.append("        CONNECT BY PRIOR T2.GROUP_ID = T2.PARENT_GROUP_ID) T1,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP_R R,\n");  
		sql.append("       TM_VHCL_MATERIAL TVM\n");  
		sql.append(" WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND T1.COMPANY_ID = "+companyId+"\n");  
		sql.append("   AND T1.GROUP_ID = R.GROUP_ID(+)\n");  
		sql.append("   AND TVM.NORMAL_ORDER_FLAG(+) = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND R.MATERIAL_ID = TVM.MATERIAL_ID(+)\n");  
		sql.append("UNION\n");  
		sql.append("SELECT DISTINCT T1.GROUP_ID,\n");  
		sql.append("                T1.GROUP_CODE,\n");  
		sql.append("                T1.GROUP_NAME,\n");  
		sql.append("                T1.PARENT_GROUP_ID\n");  
		sql.append("  FROM (SELECT *\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP T2\n");  
		sql.append("         START WITH T2.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAP\n");  
		sql.append("                      WHERE TAP.AREA_ID = "+areaId+")\n");  
		sql.append("        CONNECT BY PRIOR T2.PARENT_GROUP_ID = T2.GROUP_ID) T1,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP_R R,\n");  
		sql.append("       TM_VHCL_MATERIAL TVM\n");  
		sql.append(" WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND T1.COMPANY_ID = "+companyId+"\n");  
		sql.append("   AND T1.GROUP_ID = R.GROUP_ID(+)\n");  
		sql.append("   AND TVM.NORMAL_ORDER_FLAG(+) = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("   AND R.MATERIAL_ID = TVM.MATERIAL_ID(+)\n");

		List<TmVhclMaterialGroupPO> list=factory.select(sql.toString(), null,
				new DAOCallback<TmVhclMaterialGroupPO>() {
			public TmVhclMaterialGroupPO wrapper(ResultSet rs, int idx) {
				TmVhclMaterialGroupPO bean = new TmVhclMaterialGroupPO();
				try {
					bean.setGroupId(Long.valueOf(rs.getString("GROUP_ID")));
					bean.setParentGroupId(Long.valueOf(rs.getString("PARENT_GROUP_ID")));
					bean.setGroupName(rs.getString("GROUP_NAME"));
					bean.setGroupCode(rs.getString("GROUP_CODE"));
				} catch (SQLException e) {
					throw new DAOException(e);
				}
				return bean;
			}
		});
		return list;
	}

/**
	 * Function         : 查询物料
	 * @param           : 车系、车型、配置ID
	 * @param           : 物料CODE
	 * @param           : 物料名称
	 */
	public static PageResult<Map<String, Object>> getMaterialListByAreaId(String areaId,String ids,String groupId,String materialCode,String materialName,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();

		sql.append("SELECT TVM.MATERIAL_CODE,TVM.MATERIAL_ID,TVM.MATERIAL_NAME\n");
		sql.append("  FROM TM_VHCL_MATERIAL TVM, TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
		sql.append(" WHERE TVM.MATERIAL_ID = TVMGR.MATERIAL_ID\n"); 
		sql.append("   AND TVM.RUSH_ORDER_FLAG = "+Constant.NASTY_ORDER_REPORT_TYPE_01+"\n");   
		sql.append("   AND TVM.ORDER_FLAG = "+Constant.NASTY_ORDER_REPORT_TYPE_01+"\n");   
		sql.append("   AND TVM.STATUS = "+Constant.STATUS_ENABLE+"\n"); 
		sql.append("   AND TVM.COMPANY_ID = "+companyId+"\n");
		sql.append("   AND TVMGR.GROUP_ID IN\n");  
		sql.append("       (select T1.GROUP_ID\n");  
		sql.append("          from tm_vhcl_material_group t1\n");  
		sql.append("         WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         start with t1.group_id IN\n");  
		sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM tm_area_group tap\n");  
		sql.append("                      where tap.area_id = "+areaId+")\n");  
		sql.append("        connect by prior t1.group_id = t1.parent_group_id)");

		if(!"".equals(materialCode)&&materialCode!=null)
		{
			sql.append("   AND TVM.MATERIAL_CODE LIKE '%"+materialCode+"%'\n");	
		}
		if(!"".equals(materialName)&&materialName!=null)
		{ 
		sql.append("   AND TVM.MATERIAL_NAME LIKE '%"+materialName+"%'\n");
		}
		if(ids!=null&&!"".equals(ids)){
			sql.append("   AND TVM.MATERIAL_ID NOT IN ("+ids+")");
		}
		if(!"".equals(groupId)&&groupId!=null){
			sql.append(" AND TVMGR.GROUP_ID IN\n");
			sql.append("       (SELECT T.GROUP_ID\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP T\n");  
			sql.append("         WHERE T.STATUS = "+Constant.STATUS_ENABLE+"\n");  
			sql.append("         START WITH T.GROUP_ID = "+groupId+"\n");  
			sql.append("        CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID)");
		}

		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	
	public static PageResult<Map<String, Object>> getMaterialListByAreaIdAndOrderType(String areaId,String ids,String groupId,String materialCode,String materialName,String orderType,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();

		sql.append("SELECT TVM.MATERIAL_CODE,TVM.MATERIAL_ID,TVM.MATERIAL_NAME\n");
		sql.append("  FROM TM_VHCL_MATERIAL TVM, TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
		sql.append(" WHERE TVM.MATERIAL_ID = TVMGR.MATERIAL_ID\n"); 
		if(orderType != null && !orderType.equals("")){
			if(Integer.parseInt(orderType) == Constant.ORDER_TYPE_01.intValue()){
				sql.append("   AND TVM.NORMAL_ORDER_FLAG = "+Constant.IF_TYPE_YES+"\n");   
			}
			else if(Integer.parseInt(orderType) == Constant.ORDER_TYPE_02.intValue()){
				sql.append("   AND TVM.RUSH_ORDER_FLAG = "+Constant.NASTY_ORDER_REPORT_TYPE_01+"\n");   
			}
			else if(Integer.parseInt(orderType) == Constant.ORDER_TYPE_03.intValue()){
				sql.append("   AND TVM.SPECIAL_ORDER_FLAG = "+Constant.IF_TYPE_YES+"\n");   
			}
		}
		sql.append("   AND TVM.ORDER_FLAG = "+Constant.NASTY_ORDER_REPORT_TYPE_01+"\n");   
		sql.append("   AND TVM.STATUS = "+Constant.STATUS_ENABLE+"\n"); 
		sql.append("   AND TVM.COMPANY_ID = "+companyId+"\n");
		sql.append("   AND TVMGR.GROUP_ID IN\n");  
		sql.append("       (select T1.GROUP_ID\n");  
		sql.append("          from tm_vhcl_material_group t1\n");  
		sql.append("         WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         start with t1.group_id IN\n");  
		sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM tm_area_group tap\n");  
		sql.append("                      where tap.area_id = "+areaId+")\n");  
		sql.append("        connect by prior t1.group_id = t1.parent_group_id)");

		if(!"".equals(materialCode)&&materialCode!=null)
		{
			sql.append("   AND TVM.MATERIAL_CODE LIKE '%"+materialCode+"%'\n");	
		}
		if(!"".equals(materialName)&&materialName!=null)
		{ 
		sql.append("   AND TVM.MATERIAL_NAME LIKE '%"+materialName+"%'\n");
		}
		if(ids!=null&&!"".equals(ids)){
			sql.append("   AND TVM.MATERIAL_ID NOT IN ("+ids+")");
		}
		if(!"".equals(groupId)&&groupId!=null){
			sql.append(" AND TVMGR.GROUP_ID IN\n");
			sql.append("       (SELECT T.GROUP_ID\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP T\n");  
			sql.append("         WHERE T.STATUS = "+Constant.STATUS_ENABLE+"\n");  
			sql.append("         START WITH T.GROUP_ID = "+groupId+"\n");  
			sql.append("        CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID)");
		}

		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	
	/**
	 * Function         : 查询物料
	 * @param           : 车系、车型、配置ID
	 * @param           : 物料CODE
	 * @param           : 物料名称
	 * 微车
	 */
	public static PageResult<Map<String, Object>> getMaterialListByAreaId_Mini(String areaId,String ids,String groupId,String materialCode,String materialName,int curPage,int pageSize,Long companyId)
	{
		StringBuffer sql=new StringBuffer();

		sql.append("SELECT TVM.MATERIAL_CODE,TVM.MATERIAL_ID,TVM.MATERIAL_NAME\n");
		sql.append("  FROM TM_VHCL_MATERIAL TVM, TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
		sql.append(" WHERE TVM.MATERIAL_ID = TVMGR.MATERIAL_ID\n"); 
		// sql.append("   AND TVM.RUSH_ORDER_FLAG = "+Constant.NASTY_ORDER_REPORT_TYPE_01+"\n");   
		sql.append("   AND TVM.ORDER_FLAG = "+Constant.NASTY_ORDER_REPORT_TYPE_01+"\n");   
		sql.append("   AND TVM.STATUS = "+Constant.STATUS_ENABLE+"\n"); 
		sql.append("   AND TVM.COMPANY_ID = "+companyId+"\n");
		sql.append("   AND TVMGR.GROUP_ID IN\n");  
		sql.append("       (select T1.GROUP_ID\n");  
		sql.append("          from tm_vhcl_material_group t1\n");  
		sql.append("         WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         start with t1.group_id IN\n");  
		sql.append("                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM tm_area_group tap\n");  
		sql.append("                      where tap.area_id = "+areaId+")\n");  
		sql.append("        connect by prior t1.group_id = t1.parent_group_id)");

		if(!"".equals(materialCode)&&materialCode!=null)
		{
			sql.append("   AND TVM.MATERIAL_CODE LIKE '%"+materialCode+"%'\n");	
		}
		if(!"".equals(materialName)&&materialName!=null)
		{ 
		sql.append("   AND TVM.MATERIAL_NAME LIKE '%"+materialName+"%'\n");
		}
		if(ids!=null&&!"".equals(ids)){
			sql.append("   AND TVM.MATERIAL_ID NOT IN ("+ids+")");
		}
		if(!"".equals(groupId)&&groupId!=null){
			sql.append(" AND TVMGR.GROUP_ID IN\n");
			sql.append("       (SELECT T.GROUP_ID\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP T\n");  
			sql.append("         WHERE T.STATUS = "+Constant.STATUS_ENABLE+"\n");  
			sql.append("         START WITH T.GROUP_ID = "+groupId+"\n");  
			sql.append("        CONNECT BY PRIOR T.GROUP_ID = T.PARENT_GROUP_ID)");
		}
		sql.append("   AND TVM.NORMAL_ORDER_FLAG = "+Constant.IF_TYPE_YES+"\n");

		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, dao.getFunName(),pageSize, curPage);
		return rs;
	}
	public static List<Map<String,Object>> getTreeCodeByAreaId(String area_id){
		StringBuffer sql=new StringBuffer();
		if (null != area_id && !"".equals(area_id)) {
			sql.append("SELECT G.TREE_CODE, LENGTH (G.TREE_CODE) CODE_LENGTH \n");
			sql.append("  FROM TM_VHCL_MATERIAL_GROUP G \n");
			sql.append(" WHERE G.GROUP_ID IN (SELECT AG.MATERIAL_GROUP_ID \n");
			sql.append("                        FROM TM_AREA_GROUP AG \n");
			sql.append("                       WHERE AG.AREA_ID = "+area_id+") \n");
			return dao.pageQuery(sql.toString(), null, dao.getFunName());
		}else {
			return null;
		}
		
	}
	
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
	// 查询所有能与业务范围相关联的车系
	public List<Map<String,Object>> MateGroupQuery() {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("select tvmg.group_id, tvmg.group_code, tvmg.group_name, tag.area_id\n");
		sql.append("  from tm_vhcl_material_group tvmg, TM_AREA_GROUP tag\n");  
		sql.append(" where tvmg.group_level = 2\n");  
		sql.append("   and tvmg.group_id = tag.material_group_id\n");
		sql.append(" order by tvmg.group_name,tvmg.group_code,tvmg.group_id\n");

		return dao.pageQuery(sql.toString(), null, dao.getFunName());
	}
	/**
	 * Function         : 查询当前经销商
	 * @param           : 职位ID
	 */
	public static List<Map<String, Object>> getDealerId(String comId,String poseId)
	{
		List<Map<String, Object>> list = null;
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT TBA.AREA_CODE, TBA.AREA_ID, TBA.AREA_NAME, TD.DEALER_ID,TD.DEALER_CODE\n");
		sql.append("  FROM TC_POSE                 TP,\n");  
		sql.append("       TM_DEALER               TD,\n");  
		sql.append("       TM_DEALER_BUSINESS_AREA TDBA,\n");  
		sql.append("       TM_BUSINESS_AREA        TBA,\n"); 
		sql.append("       TM_POSE_BUSINESS_AREA   TPBA\n"); 
		sql.append(" WHERE TP.ORG_ID = TD.DEALER_ORG_ID\n");  
		sql.append("   AND TD.DEALER_ID = TDBA.DEALER_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TPBA.POSE_ID = TP.POSE_ID\n");  
		sql.append("   AND TDBA.AREA_ID = TPBA.AREA_ID\n");  
		sql.append("   AND TD.STATUS = "+Constant.STATUS_ENABLE+"\n");
		sql.append("   AND TBA.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		//sql.append("   AND TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+"\n");
		sql.append("   AND (TD.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+" OR TD.DEALER_TYPE = "+Constant.DEALER_TYPE_QYZDL+")\n");
		if(!"".equals(poseId)&&poseId!=null)
		{
			sql.append("   AND TP.POSE_ID = "+poseId+"\n");;	
		}
		if(!"".equals(comId)&&comId!=null)
		{
			sql.append("   AND TD.Company_ID = "+comId+"\n");;	
		}
		sql.append("   ORDER BY TD.DEALER_NAME,TD.DEALER_CODE\n");
		
		
		list=dao.pageQuery(sql.toString(), null, dao.getFunName());
		return list;
	}
	/**
	 * 查询月度常规订单提报的起止日期
	 * */
	public static List<Map<String,Object>> getCommonOrderDate(Long oemCompanyId){
		StringBuffer sql=new StringBuffer();
		if (null != oemCompanyId && !"".equals(oemCompanyId)) {
			sql.append("SELECT BP.PARA_VALUE\n");
			sql.append("  FROM TM_BUSINESS_PARA BP\n");  
			sql.append(" WHERE BP.OEM_COMPANY_ID = "+oemCompanyId+"\n");  
			sql.append("   AND BP.TYPE_CODE = 2021\n");  
			sql.append(" ORDER BY BP.PARA_ID\n");

			return dao.pageQuery(sql.toString(), null, dao.getFunName());
		}else {
			return null;
		}
		
	}
	
	
	// 查询所有下级物料组
	public List<Map<String,Object>> querySubMaterialGroupList(String poseId, String groupId, String groupLevel, String isAllArea) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("select T.GROUP_ID,\n" );
		sql.append("       T.GROUP_CODE,\n" );
		sql.append("       T.GROUP_NAME,\n" );
		sql.append("       T.PARENT_GROUP_ID,\n" );
		sql.append("       T.TREE_CODE,\n" );
		sql.append("       T.GROUP_LEVEL\n" );
		sql.append("from tm_vhcl_material_group t\n" );
		sql.append("where 1=1\n" );
		sql.append("and t.parent_group_id = "+groupId+"\n" );
		sql.append("and t.group_level = ("+groupLevel+"+1)");
		
		if(!isAllArea.toLowerCase().equals("true")){
			String areaIds = getAreaIdsByPoseId(new Long(poseId));
			
			if(areaIds !=null && !areaIds.equals("")){
				sql.append(" AND T.GROUP_ID IN\n");
				sql.append("    (select T.group_id\n");  
				sql.append("       from tm_vhcl_material_group t\n");  
				sql.append("      start with T.group_id IN\n");  
				sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
				sql.append("                    FROM tm_area_group tap\n");  
				sql.append("                   where tap.area_id in ("+areaIds+"))\n");  
				sql.append("     connect by prior T.group_id = T.parent_group_id\n");  
				sql.append("     union\n");  
				sql.append("     select T.group_id\n");  
				sql.append("       from tm_vhcl_material_group t\n");  
				sql.append("      start with T.group_id IN\n");  
				sql.append("                 (SELECT TAP.MATERIAL_GROUP_ID\n");  
				sql.append("                    FROM tm_area_group tap\n");  
				sql.append("                   where tap.area_id in ("+areaIds+"))\n");
				sql.append("     connect by prior T.parent_group_id = T.group_id)");
			}
		}

		return dao.pageQuery(sql.toString(), null, dao.getFunName());
	}
}
